package il.ac.braude.sona.ocsf.server;

import il.ac.braude.sona.constants.DatabaseScheme;
import il.ac.braude.sona.constants.P;
import il.ac.braude.sona.constants.Resources;
import il.ac.braude.sona.constants.Statuses;
import il.ac.braude.sona.constants.TestingParamKit;
import il.ac.braude.sona.entities.ChangeRequest;
import il.ac.braude.sona.entities.ExtensionRequest;
import il.ac.braude.sona.entities.FileTransfer;
import il.ac.braude.sona.entities.Messege;
import il.ac.braude.sona.entities.PeriodicReport;
import il.ac.braude.sona.entities.SupervisorAction;
import il.ac.braude.sona.entities.User;
import il.ac.braude.sona.entities.Worker;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map.Entry;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;

/**
 * This class contains all methods for interaction with DB and execute SQL queries
 * @author  Ofek Ron
 * @author Avihay Avitan
 * @author Nitzan Inbar
 * @author Sasha Rozenthal
 * Group 6,<br>Laboratory in Software Engineering,Ort Braude College.</p>
 *
 */

public class DatabaseController {
	private TestingParamKit myKit=TestingParamKit.getTestingKit();
	private static DatabaseController instance;
	public static final String dbName = "BraudeSystem";
	public static String useQuery = "USE " + dbName;
	public static  String connectURL = "jdbc:mysql://85.64.56.233:3306/?user=root&password=1234";

	/**
	 * Class constructor.
	 */
	private DatabaseController() {
	}
/**
 * Creates connection to database instance
 * @param ip db ip
 * @param username db username
 * @param password db password
 * @return DatabaseController
 */
	static DatabaseController getInstance(String ip,String username,String password) {
		connectURL="jdbc:mysql://"+ip + ":3306/?user="+username + "&password="+password;
		return instance == null ? instance = new DatabaseController() : instance;
	}
	/**
	 * Checks if the user in the users db 
	 * @param values uid and password
	 * @return session 
	 * @throws SQLException
	 */	
	public User login(ArrayList<String> values) throws SQLException {
		return login(values.get(0), values.get(1));
	}
/**
 * Checks if the user in the users db 
 * @param uid user id
 * @param pw password
 * @return session 
 * @throws SQLException
 */	
	public User login(String uid, String pw) throws SQLException {
		Statement stmt = null;
		String query = "select uid,fname,lname,tid "
				+ "from Users where uid=\"" + uid + "\""
				+ " and password=\"" + pw + "\"";
		ResultSet rs = null;
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(query);
			if (!rs.next())
				return null;
			return new User(rs,getPermissions(rs.getString("tid")));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null)
				conn.close();
		}
		return null;
	}
	/**
	 * Returns workers by role
	 * @param tid role
	 * @return workers list
	 */
	public ArrayList<Worker> getWorkers(String tid) {

		PreparedStatement statement = null;
		String queryForInfo = "select fname,lname,tid,Email from users where tid="+tid+
				" OR tid = 'b.member' OR tid = 'b.chairman' OR tid = 'supervisor'";
		ArrayList<Worker> res = new ArrayList<Worker>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForInfo);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(queryForInfo);
			while (rs.next())
			{
				Worker w =new Worker(rs.getString("fname"),rs.getString("lname"),rs.getString("tid"),rs.getString("Email"));
				res.add(w);
			}
				return res;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;

	}
	/**
	 * Returns permissions per position for main menu
	 * @param tid position
	 * @return permissions
	 * @throws SQLException
	 */
	private HashSet<String> getPermissions(String tid) throws SQLException {
		Statement stmt = null;
		String query = "select pid "
				+ "from premissions where tid=\"" + tid + "\"";
		ResultSet rs = null;
		HashSet<String> result = new HashSet<String>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(query);
			while (rs.next()) result.add(rs.getString("pid"));
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null)
				conn.close();
		}
		return null;
	}
/**
 * Creates change request.
 * @param values change request data
 * @param id request id to check
 * @return request id
 * @throws SQLException
 */
	public long createRequest(ArrayList<String> values, String id) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO  change_request (title ,sysid,cur_state,sug_change,explan,comment,ownerid,sid,date_created) VALUES ( '"
				+ values.get(0);
		for (int i = 1; i < values.size(); i++)
			query += "','" + values.get(i);
		query += "','"+id+"','" +Statuses.New_Request +"',CURDATE());";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			if (affectedRows == 0)
				throw new SQLException(
						"Creating user failed, no rows affected.");
			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}
/**
 * Returns request details
 * @param rid request id
 * @return details
 */
	public ChangeRequest getRequestDetails(long rid) {
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "select * from change_request"
				+ " where rid=\"" + rid + "\"";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if (!rs.next())
				return null;
			return new ChangeRequest(rs,conn.prepareStatement(getFilesQuery(rid+"")).executeQuery());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;
	}
/**
 * Returns user data from role
 * @param tid user's role
 * @return user data
 */
	public HashMap<String,String> getUsersNameWhereTidLike(String tid) {
		PreparedStatement statement = null;
		String queryForInfo = "select uid,fname,lname from users where tid="+tid;
		HashMap<String,String> res = new HashMap<String,String>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForInfo);
			statement.execute(useQuery);

			ResultSet usersName = statement.executeQuery(queryForInfo);
			while (usersName.next())
				res.put(usersName.getNString("fname")+" "+usersName.getNString("lname"),usersName.getNString("uid"));
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}
/**
 * Returns all change requests.
 * @return all change requests.
 */
	public ArrayList<ChangeRequest> getAllRequestDetails() {
		PreparedStatement statement = null;
		String queryForInfo = "select * from change_request";
		ArrayList<ChangeRequest> res = new ArrayList<ChangeRequest>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForInfo);
			statement.execute(useQuery);

			ResultSet changeInfo = statement.executeQuery(queryForInfo);
			while (changeInfo.next())
				res.add(new ChangeRequest(changeInfo,conn.prepareStatement(getFilesQuery(changeInfo.getString("rid"))).executeQuery()));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}

/**
 * Returns attached files to request.
 * @param rid request id
 * @return filenames
 */
	private String getFilesQuery(String rid) {
		return "select fid from files_in_change_request where rid='"+rid+"';";
	}


/**
 * Tests DB connection
 * @return true if connection is ok
 */
	public boolean testConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);

		} catch (SQLException e) {
			return false;
		} finally {
			try {
				if (conn!=null) conn.close();
			} catch (SQLException e) {
			}
		}
		return true;
	}

	/**
	 * Adds file to request
	 * @param f file to add.
	 * @throws SQLException
	 */
	public void addFileToDatabase(FileTransfer f) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO  files_in_change_request (rid,fid) VALUES ( '"
				+ f.getChangeRequestId() + "','"+f.toString()+"');";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			statement.executeUpdate(query);
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}
/**
 * Returns all existing information systems
 * @return  all existing information systems.
 */
	public ArrayList<String> getSystemsAvailable() {
		PreparedStatement statement = null;
		String query = "select * from info_system";
		ArrayList<String> res = new ArrayList<String>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			while (rs.next())
				res.add(rs.getString("sysid"));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}

	/**
	 * Returns worker assigned for information system.
	 * @param system information system to get appointed worker.
	 * @return worker data.
	 */
	
	public Object getSystemWorker(String system) {
		PreparedStatement statement = null;
		String queryForInfo = "select u.fname,u.lname from users u,info_system s where s.sysid="+system+" and s.uid=u.uid";
		String res = new String();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForInfo);
			statement.execute(useQuery);

			ResultSet usersName = statement.executeQuery(queryForInfo);
			while (usersName.next())
				res+=usersName.getNString("fname")+" "+usersName.getNString("lname");
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;	
	}

	/**
	 * Returns information to delays report.
	 * @return information for delays report.
	 */
	public HashMap<String,String> totalDelays()
	{
		String sql = "Select count(*) 'Total delays' from braudesystem.status_execution where datediff(date_finished,date_started)>extension_time+estimate_time" +
				" or (date_finished is null and datediff( Current_DATE(),(ADDDATE(date_started,extension_time+estimate_time))) > 0);";
		Statement stmt = null;
		String stDev = "0";
		Integer sum = 0;
		HashMap<String,String> ret = new HashMap<String,String>();
		ArrayList<Integer> set = new ArrayList<Integer>();
		Connection conn = null;
		try 
		{
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.prepareStatement(sql);
			ResultSet rs;
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("Total delays");
			}
			ret.put("Total request delayed", stDev);
			sql = "Select sum(datediff(date_finished,date_started)-estimate_time-extension_time) 'Total delays' from braudesystem.status_execution where datediff(date_finished,date_started)>estimate_time+extension_time;";
			stmt.execute(useQuery);
			stmt.execute(sql);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				sum = rs.getInt("Total delays");
			}
			sql = "Select sum(datediff(Current_DATE(),date_started)-estimate_time-extension_time) 'Total delays' from braudesystem.status_execution where date_finished is null and datediff(Current_DATE(),date_started)>estimate_time+extension_time;";
			stmt.execute(useQuery);
			stmt.execute(sql);
			rs = stmt.executeQuery(sql);
			if( rs.next() ) 
				sum += rs.getInt("Total delays");
			
			ret.put("Total days delayed", sum.toString());
			sql="SELECT STDDEV(DATEDIFF(date_finished,date_started)-estimate_time-extension_time) 'Devi' FROM braudesystem.status_execution where datediff(date_finished,date_started)>estimate_time+extension_time;";
			stmt.execute(useQuery);
			stmt.execute(sql);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("Devi");
			}
			ret.put("Total standard deviation.", stDev);
//************************************************************************
			final String system[] = {"Braude web-site","Moodle","Student info. station","Worker info. station","Lecturer info. station",
	    			"Class computers","Labs & guests PCs","Library System" };
			for(int i=0; i<system.length;i++)
			{
				sql = "SELECT STDDEV(DATEDIFF(date_finished,date_started)-estimate_time-extension_time) 'Devi' FROM braudesystem.status_execution se,braudesystem.change_request cr where (datediff(date_finished,date_started)>estimate_time+extension_time) and cr.rid = se.rid and sysid='";
				sql+=system[i];
				sql+="';";
				stmt.execute(useQuery);
				stmt.execute(sql);
				rs = stmt.executeQuery(sql);
				while ( rs.next() ) 
				{
					stDev = rs.getString("Devi");
				}
				ret.put("Standard devaition for delayed days "+system[i], stDev);
			}
//************************************************************************			
			sql="Select datediff(date_finished,date_started)-estimate_time-extension_time 'All delayed' from braudesystem.status_execution where datediff(date_finished,date_started)>extension_time+estimate_time;";
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				set.add(rs.getInt("All delayed"));
			}
			int tmp=0;
			Collections.sort(set);
			Iterator<Integer> it = set.iterator();
			if(set.size()==1)
				tmp=it.next();
			else
				for( int i =0 ; i < set.size()/2 ; i++)
					tmp=it.next();
			
			stDev="";
			stDev+=tmp;
			ret.put("Median for all", stDev);
			HashMap<String,String> test = frequency();
			ret.putAll(test);
			return ret;
		
			} 
		catch (SQLException e) {
			e.printStackTrace();
		} 
		finally 
		{
			if (stmt != null)
				try { stmt.close(); } 
			catch (SQLException logOrIgnore) { }
			if (conn != null)
				try {conn.close();} 
			catch (SQLException logOrIgnore) {}
		}
		return ret;
	}
	/**
	 * Returns statistic frequency for reports.
	 * @return statistic frequency by system.
	 */
	public HashMap<String, String> frequency()
	{
		Integer day,fre;
		//String b = "all";
		String sql;
			 sql = "SELECT change_request.sysid,sum(DATEDIFF(date_finished,date_started)-estimate_time-extension_time) 'days' , COUNT(*) AS frequency "+
					" FROM braudesystem.status_execution,braudesystem.change_request,braudesystem.exceptions "+
					" where status_execution.rid = change_request.rid and  exceptions.executionid = status_execution.eid and " +
					" DATEDIFF(status_execution.date_finished,status_execution.date_started)-status_execution.estimate_time-status_execution.extension_time > 0  " +
					" GROUP BY change_request.sysid;";
			 String sqlnew = "SELECT change_request.sysid,sum(DATEDIFF(Current_DATE(),date_started)-estimate_time-extension_time) 'days' , COUNT(*) AS frequency "+
						" FROM braudesystem.status_execution,braudesystem.change_request,braudesystem.exceptions "+
						" where status_execution.rid = change_request.rid and  exceptions.executionid = status_execution.eid and " +
					 " date_finished is null and datediff( Current_DATE(),date_started)>extension_time+estimate_time  " +
								" GROUP BY change_request.sysid;";
		Statement stmt = null;
		HashMap<String,String> ret = new HashMap<String,String>();		
		Connection conn = null;
		String days,freq;
		try {	
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			ResultSet rs;
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
			    days = rs.getString("days");
			    freq = rs.getString("frequency");
			
				if(Integer.parseInt(days) >= 0 &&Integer.parseInt(freq) > 0)
				{
					ret.put("Number of stage delays "+rs.getString("sysid"), freq);
					ret.put("Number of delayed days "+rs.getString("sysid"), days);
				}
			}
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sqlnew);
			while ( rs.next() ) 
			{
			    days = rs.getString("days");
			    freq = rs.getString("frequency");
			  
				if(Integer.parseInt(days) >= 0 &&Integer.parseInt(freq) > 0)
				{
					if (ret.containsKey("Number of delayed days "+rs.getString("sysid")))
					{
					  day = Integer.parseInt(days) + Integer.parseInt(ret.get("Number of delayed days "+rs.getString("sysid")));
					  ret.put("Number of delayed days "+rs.getString("sysid"), day.toString());
					}
					else 
						ret.put("Number of delayed days "+rs.getString("sysid"), days);
					if (ret.containsKey("Number of stage delays "+rs.getString("sysid")))
					{
					  fre = Integer.parseInt(freq) + Integer.parseInt(ret.get("Number of stage delays "+rs.getString("sysid")));
					  ret.put("Number of stage delays "+rs.getString("sysid"), fre.toString());
					}
					else 
						ret.put("Number of stage delays "+rs.getString("sysid"), freq);  
				}
			}
            
			return ret;
		} catch (SQLException e) { e.printStackTrace();} 
		finally {
			if (stmt != null)
				try {stmt.close();} catch (SQLException logOrIgnore) {}
			if (conn != null)
				try {conn.close();} catch (SQLException logOrIgnore) {}
		}
		return null;
		
	}
	/**
	 * Returns data for extensions report.
	 * @param values 
	 * @return data for extensions report.
	 */
	
	public HashMap<String,String> extensionsReport(ArrayList<String> values)
	{
		String sql = "SELECT sum(extension_days) 'approved' FROM braudesystem.extension_request where decision = 1;";
		Statement stmt = null;
		String stDev = "0";
		HashMap<String,String> ret = new HashMap<String,String>();
		Connection conn = null;
		try {	
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			ResultSet rs;
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("approved");
			}
			

			ret.put("Extension days approved", stDev);
			sql = "SELECT count(*) 'approved' FROM braudesystem.extension_request where decision = 1;";


			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("approved");
			}
			ret.put("Number of approved extensions", stDev);
			sql = "SELECT count(DATEDIFF(date_finished,date_started)) 'Returned requests' FROM braudesystem.status_execution where sid='Additional Surveying' or eid in (select e.eid from status_execution e where e.sid='Execution Started' and e.eid not in (Select min(e2.eid) from change_request ch,status_execution e2 where  e2.sid='Execution Started' and e2.rid=ch.rid group by e2.rid ));";

			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("Returned requests");
			}
			ret.put("Returned requests", stDev);
			sql = "SELECT sum(DATEDIFF(date_finished,date_started)) 'Days added' FROM braudesystem.status_execution where sid='Additional Surveying' or braudesystem.status_execution.eid in (select e.eid from braudesystem.status_execution e where e.sid='Execution Started' and e.eid not in (Select min(e2.eid) from braudesystem.change_request ch,status_execution e2 where  e2.sid='Execution Started' and e2.rid=ch.rid group by e2.rid ));";
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
			{
				stDev = rs.getString("Days added");
			}
			ret.put("Days added", stDev);				
			
			return ret;
		} catch (SQLException e) { e.printStackTrace();} 
		finally {
			if (stmt != null)
				try {stmt.close();} catch (SQLException logOrIgnore) {}
			if (conn != null)
				try {conn.close();} catch (SQLException logOrIgnore) {}
		}
		return null;
	}
/**
 * Returns data for periodic report.
 * @param dates dates to check
 * @return data for periodic report.
 */
	
	
	public HashMap<String, HashMap<String, String>> actionReport(ArrayList<String>  dates) {
		System.out.println(dates.get(1)+"  "+dates.get(2));
	
		HashMap<String,HashMap<String,String>> result = new HashMap<String,HashMap<String,String>>(); 
		String sql = "select mydate.dte,count(distinct e.rid) as num from status_execution e,(SELECT '"+dates.get(1)+"' + INTERVAL a + b DAY dte FROM " + Resources.Strings.DateListQuesry +" m WHERE '"+dates.get(1)+"' + INTERVAL a + b DAY  <  '"+dates.get(2)+"' ORDER BY a + b) mydate where e.date_started<=mydate.dte and (e.date_finished>=mydate.dte or e.date_finished is null) group by mydate.dte";
	    String sqldates = "SELECT '"+dates.get(1)+"' + INTERVAL a + b DAY dte FROM (SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7  UNION SELECT 8 UNION SELECT 9 ) d,(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 UNION SELECT 100 UNION SELECT 110 UNION SELECT 120 UNION SELECT 130 UNION SELECT 140 UNION SELECT 150 UNION SELECT 160 UNION SELECT 170 UNION SELECT 180) m WHERE '"+dates.get(1)+"' + INTERVAL a + b DAY  <  '"+dates.get(2)+"' ORDER BY a + b ";
		Statement stmt = null;
		String stDev = null;
		HashMap<String,String> ret = new HashMap<String,String>();
		
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			ResultSet rs;
			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sqldates);
			while ( rs.next() ) 
				ret.put(rs.getString("dte"),"0");
			
			
		
		//	stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
				ret.put(rs.getString("dte"),rs.getString("num"));
			result.put("Active", ret);
			ret= new HashMap<String,String>();
		
			//***************************************************************************EXECUTED
			sql = "select mydate.dte,count(distinct e.rid) as num from status_execution e, change_request ch,(SELECT '"+dates.get(1)+"' + INTERVAL a + b DAY dte FROM " + Resources.Strings.DateListQuesry +" m WHERE '"+dates.get(1)+"' + INTERVAL a + b DAY  <  '"+dates.get(2)+"' ORDER BY a + b) mydate where e.sid='Closing Request' and e.date_finished<=mydate.dte and e.date_finished>='"+dates.get(1)+"' and e.rid=ch.rid and ch.sid='Closed Executed' group by mydate.dte";
			stmt = null;
			stDev = null;

			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(sqldates);
			while ( rs.next() ) 
				ret.put(rs.getString("dte"),"0");
			
		
				
				stmt = conn.createStatement();
				stmt.execute(useQuery);
				rs = stmt.executeQuery(sql);
				while ( rs.next() ) 
					ret.put(rs.getString("dte"),rs.getString("num"));
				result.put("Executed", ret);
				ret= new HashMap<String,String>();
			
				//***************************************************************************REJECTED
				sql = "select mydate.dte,count(distinct e.rid) as num from status_execution e, change_request ch,(SELECT '"+dates.get(1)+"' + INTERVAL a + b DAY dte FROM " + Resources.Strings.DateListQuesry +" m WHERE '"+dates.get(1)+"' + INTERVAL a + b DAY  <  '"+dates.get(2)+"' ORDER BY a + b) mydate where e.sid='Closing Request' and e.date_finished<=mydate.dte and e.date_finished>='"+dates.get(1)+"' and e.rid=ch.rid and ch.sid='Closed Rejected' group by mydate.dte";
				stmt = null;
				System.out.println(sql);
				stDev = null;
				stmt = conn.createStatement();
				stmt.execute(useQuery);
				rs = stmt.executeQuery(sqldates);
				while ( rs.next() ) 
					ret.put(rs.getString("dte"),"0");
				
				
				
					stmt = conn.createStatement();
					stmt.execute(useQuery);
					rs = stmt.executeQuery(sql);
					while ( rs.next() ) 
						ret.put(rs.getString("dte"),rs.getString("num"));
					
					//***************************************************************************FREEZED
					result.put("Rejected", ret);
					ret= new HashMap<String,String>();
					
				sql = "select mydate.dte,count(distinct f.rid) as num from status_execution_freezed f ,(SELECT '"+dates.get(1)+"' + INTERVAL a + b DAY dte FROM " + Resources.Strings.DateListQuesry +" m WHERE '"+dates.get(1)+"' + INTERVAL a + b DAY  <  '"+dates.get(2)+"' ORDER BY a + b) mydate where f.date_freezed_started<=mydate.dte and (f.date_freezed_finished>=mydate.dte or f.date_freezed_finished is null) group by mydate.dte";
				stmt = null;
				stDev = null;

				stmt = conn.createStatement();
				stmt.execute(useQuery);
				rs = stmt.executeQuery(sqldates);
				while ( rs.next() ) 
					ret.put(rs.getString("dte"),"0");
				
				
				
					stmt = conn.createStatement();
					stmt.execute(useQuery);
					rs = stmt.executeQuery(sql);
					while ( rs.next() ) 
						ret.put(rs.getString("dte"),rs.getString("num"));
					result.put("Suspended", ret);
					ret= new HashMap<String,String>();
			//******************************************************************************
			sql = "SELECT sum(datediff(date_finished,date_started)+0.1) 'Total days spent' FROM braudesystem.status_execution where date_finished<>'null' and date_started>='";
			sql+=(dates.get(1));
			sql+="';";
			stmt.execute(useQuery);
			stmt.execute(sql);
			rs = stmt.executeQuery(sql);
			while ( rs.next() ) 
				stDev = rs.getString("Total days spent");
			ret.put("Total days spent", stDev);
			result.put("Total", ret);
			ret= new HashMap<String,String>();
			ret.put(dates.get(1), dates.get(2));
			result.put("dates", ret);
			
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return result;

	}
	
	/**
	 * Return actions made by supervisor. 
	 * @return actions made by supervisor. 
	 */
public ArrayList<SupervisorAction> supervisorData()
{
	String sql = "SELECT * FROM braudesystem.supervisor_update_record;";
	Statement stmt = null;
	ArrayList<SupervisorAction> lst = new ArrayList<SupervisorAction>();
//	supervisorAction ret = new supervisorAction ("","","","","");		
	Connection conn = null;
	try 
	{	
		conn = DriverManager.getConnection(connectURL);
		stmt = conn.createStatement();
		ResultSet rs;
		stmt.execute(useQuery);
		rs = stmt.executeQuery(sql);
		while ( rs.next() ) 
		{
			lst.add(new SupervisorAction( rs.getString("record_number"),rs.getString("rid"),rs.getString("upaction"),
					rs.getString("uid"),rs.getString("date")));
		}
		return lst;
	} catch (SQLException e) { e.printStackTrace();} 
	finally 
	{
		if (stmt != null)
			try {stmt.close();} catch (SQLException logOrIgnore) {}
		if (conn != null)
			try {conn.close();} catch (SQLException logOrIgnore) {}
	}
	return null;
}

/**
 * Appoints worker to request.
 * @param values a list with request id, role id, worker id. 
 * @throws SQLException
 */
	public void appointReqWorkers(ArrayList<String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "INSERT INTO  worker_type_in_request (rid ,tid,uid) VALUES" +
				"('"+values.get(0)+"','surveyor','"+values.get(1)+"'),"+
				"('"+ values.get(0)+"','execution','"+values.get(2)+"');";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0) 
				throw new SQLException(
						"Appoint workers failed, no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}


/**
 * Appoints tester to request
 * @param values a list with request id, role id, worker id.
 * @throws SQLException
 */
	public void appointReqTester(ArrayList<String> values)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "INSERT INTO  worker_type_in_request (rid ,tid,uid) VALUES" +
				"('"+ values.get(2)+"','tester','"+values.get(1)+"');";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Appoint workers failed, no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Updates request status.
 * @param reqid request id
 * @param newStatus new request status.
 * @throws SQLException
 */
	public void updateRequestStatus(String reqid,String newStatus)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "UPDATE "+DatabaseScheme.ChangeRequest.table_name+" SET sid='"+newStatus+"' WHERE rid='"+reqid+"';";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

/**
 * Inserts new user to user list.
 * @param values user parameters.
 * @throws SQLException
 */
	public void insertNewUser(ArrayList<String> values)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "INSERT INTO  "+DatabaseScheme.Users.table_name+" (uid ,password,fname,lname,Email,tid) VALUES" +
				"('"+ values.get(0)+"','"+ values.get(1)+"','"+ values.get(2)+"','"+ values.get(3)+"','"+ values.get(4)+"','"+ values.get(5)+"');";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Adding User failed, no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Updates Management Workers.
 * @param management  workers data.
 * @throws SQLException
 */
	public void updateManagementWorkers(ArrayList<String> management)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String set="";
		String uid="";
		String query = "UPDATE "+DatabaseScheme.Users.table_name+" SET tid='worker' WHERE tid='supervisor' or tid='b.chairman' or tid='b.member'; ";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
			set="supervisor";
			uid=management.get(0);
			String querynew ="UPDATE "+DatabaseScheme.Users.table_name+" SET tid='"+set+"' WHERE uid='"+uid+"'";
			affectedRows = statement.executeUpdate(querynew);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
			set="b.chairman";
			uid=management.get(1);
			querynew ="UPDATE "+DatabaseScheme.Users.table_name+" SET tid='"+set+"' WHERE uid='"+uid+"'";
			affectedRows = statement.executeUpdate(querynew);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
			set="b.member";
			uid=management.get(2);
			querynew ="UPDATE "+DatabaseScheme.Users.table_name+" SET tid='"+set+"' WHERE uid='"+uid+"'";
			affectedRows = statement.executeUpdate(querynew);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
			uid=management.get(3);
			querynew ="UPDATE "+DatabaseScheme.Users.table_name+" SET tid='"+set+"' WHERE uid='"+uid+"'";
			affectedRows = statement.executeUpdate(querynew);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

/**
 * Returns estimated time for request stage
 * @param rid request id
 * @param stage stage request is estimated for time
 * @return estimated time
 */
	public String getEstimateTime(String rid, String stage) {
		PreparedStatement statement = null;
		String queryForInfo = "select estimate_time from "+DatabaseScheme.StatusExecution.table_name+" where rid="+rid+" and sid='"+stage+"' and date_started is NULL;";
		String res = new String();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForInfo);
			statement.execute(useQuery);

			ResultSet usersName = statement.executeQuery(queryForInfo);
			while (usersName.next())
				res+=usersName.getString("estimate_time");
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;	
	}
/**
 * Updates time estimation for stage in request
 * @param rid request id
 * @param status status id
 * @param time new estimated time
 * @throws IOException
 * @throws SQLException
 */
	
	public void updateEstimateTime(String rid,String status,String time)throws IOException, SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "UPDATE "+DatabaseScheme.StatusExecution.table_name+" SET estimate_time="+time+" WHERE rid='"+rid+"' and sid='"+status+"' and date_started is NULL;";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update estimate_time , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Starts execution of stage
 * @param rid request id
 * @param status status that starts
 * @throws SQLException
 */
	public void startExecStage(String rid,String status)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "UPDATE "+DatabaseScheme.StatusExecution.table_name+" SET date_started=CURDATE() WHERE rid='"+rid+"' and sid='"+status+"' and date_started is NULL;";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update StatusExecution , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

/**
 * Finishes execution on stage
 * @param rid request id
 * @param status status to end
 * @throws SQLException
 */
	public void finishExecStage(String rid,String status)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "UPDATE "+DatabaseScheme.StatusExecution.table_name+" SET date_finished=CURDATE() WHERE rid='"+rid+"' and sid='"+status+"' and date_started is not NULL;";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update StatusExecution , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

	/**
	 * Initiates new stage execution 
	 * @param reqid request id
	 * @param newStatus new status
	 * @return long generatedKeys of stage execution row
	 * @throws SQLException
	 */
	public long initNewExecStage(String reqid,String newStatus)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO "+DatabaseScheme.StatusExecution.table_name+" (rid ,sid) VALUES ('"+reqid+"','"+newStatus+"');";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			if (affectedRows == 0)
				throw new SQLException(
						"Creating ExecStage failed, no rows affected.");
			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			} else {
				throw new SQLException(
						"Creating ExecStage failed, no generated key obtained.");
			}
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}
/**
 * Returns all unfinished requests details for user
 * @param uid user id
 * @return all unfinished requests details for user
 */
	public ArrayList<ChangeRequest> getAllPendingRequestDetails(String uid) {
		PreparedStatement statement = null;
		String queryForGeneral = "select * from " +
				"change_request cr natural join " +
				"type_status ts natural join " +
				"users u  " +
				"where u.uid='"+uid+"' and u.tid=ts.tid and ts.sid=cr.sid;";
		String queryForSpec = "select * from " +
				"worker_type_in_request wtr natural join " +
				"change_request cr natural join " +
				"type_status ts " +
				"where uid='"+uid+"' and ts.tid=wtr.tid and ts.sid=cr.sid;";
		String queryForUser = "select * from " +
				"change_request where ownerid='"+uid+"' and sid='"+Statuses.Closing_User_Request+"';";
		ArrayList<ChangeRequest> res = new ArrayList<ChangeRequest>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryForGeneral);
			statement.execute(useQuery);
			ResultSet changeInfo = statement.executeQuery(queryForGeneral);
			while (changeInfo.next()) {
				ChangeRequest cr = new ChangeRequest(changeInfo,conn.prepareStatement(getFilesQuery(changeInfo.getString("rid"))).executeQuery());
				if (!res.contains(cr)) res.add(cr);
			}
			changeInfo = statement.executeQuery(queryForSpec);
			while (changeInfo.next()) {
				ChangeRequest cr = new ChangeRequest(changeInfo,conn.prepareStatement(getFilesQuery(changeInfo.getString("rid"))).executeQuery());
				if (!res.contains(cr)) res.add(cr);
			}
			changeInfo = statement.executeQuery(queryForUser);
			while (changeInfo.next()) {
				ChangeRequest cr = new ChangeRequest(changeInfo,conn.prepareStatement(getFilesQuery(changeInfo.getString("rid"))).executeQuery());
				if (!res.contains(cr)) res.add(cr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}
/**
 * Returns the uid of the user which next status is its next turn to handle
 * @param rid - the request
 * @param newStatus - the new status
 * @return ArrayList of uids 
 */
	public ArrayList<String> getWorkerUIDForChangeRequestNewStatus(String rid,String newStatus) {
		ArrayList<String> res = new ArrayList<String>();
		String queryFromWorkerTypeInRequest = "select uid from " + 
				DatabaseScheme.WorkerTypeInRequest.table_name + " wt natural join " + 
				DatabaseScheme.TypeStatuses.table_name + " ts "
				+ "where wt.rid='"+rid+"' and wt.tid=ts.tid and ts.sid='"+newStatus+"';";
		String queryFromGeneral = "select uid from " + 
				DatabaseScheme.Users.table_name + " u natural join " + 
				DatabaseScheme.TypeStatuses.table_name + " ts "
				+"where ts.sid='"+newStatus+"' and (ts.tid<>'student' or ts.tid<>'lecturer') ;";/////////////////////////
		String queryOwner = "select ownerid as uid from " + 
				DatabaseScheme.ChangeRequest.table_name + 
				" where rid='"+rid+";";
		PreparedStatement statement = null;
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(queryFromWorkerTypeInRequest);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(queryFromWorkerTypeInRequest);
			while (rs.next()) res.add(rs.getString("uid"));
			rs = statement.executeQuery(queryFromGeneral);
			while (rs.next()) res.add(rs.getString("uid"));
			if (newStatus.equals(Statuses.Closing_User_Request)) {
				rs = statement.executeQuery(queryOwner);
				while (rs.next()) res.add(rs.getString("uid"));
			}
			return res;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;	
	}

	/**
	 * Inserts extension request for change request
	 * @param values request id,stage id, user id, extension days, explanation
	 * @return true if succeeded
	 * @throws SQLException
	 */
	
	
	public boolean insertExtensionRequest(ArrayList<String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "INSERT INTO "+DatabaseScheme.ExtensionRequest.table_name+"" +
				" (rid,sid,uid,extension_days,explanation) VALUES ('"+values.get(2)+"','"+values.get(3)+"','"
				+values.get(5)+"','"+values.get(0)+"','"+values.get(1)+"');";
		String supervisor = "SELECT uid from users where tid = 'supervisor'";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			statement = conn.prepareStatement(supervisor);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(supervisor);
			if (rs.next())
				MessegesAPI.sendExtensionMessege(rs.getString("uid"), values.get(2), null);
			if (affectedRows == 0)
				return false;
			else  {
				myKit.inDatabase = true;
				return true;
			}

		} finally {

			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}


	}
/**
 * Returns permissions for action.
 * @param query - the query that finds the permissions
 * @return HashSet contains the permissions
 * @throws SQLException
 */
	public HashSet<String> getButtonPermissions(String query) throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		HashSet<String> result = new HashSet<String>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			stmt = conn.createStatement();
			stmt.execute(useQuery);
			rs = stmt.executeQuery(query);
			while (rs.next()) result.add(rs.getString("pid"));
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null)
				conn.close();
		}
		return null;
	}
/**
 * Creates survey report
 * @param list list of parameters for the report
 * @param uid user id
 * @throws SQLException
 */
	public void createSurveyReport(ArrayList<String> list, String uid)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "Insert Into "+DatabaseScheme.SurveyReport.table_name+" Values ('"+list.get(5)+"','"+uid+"','"+list.get(6)+"','"+list.get(0)+"','"+list.get(1)+"','"+list.get(2)+"','"
				+list.get(3)+"','"+list.get(4)+"',NULL)";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update StatusExecution , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Returns survey report details
 * @param rid request id
 * @return ArrayList contains survey report data 
 * @throws SQLException
 */
	public ArrayList<String> getSurveyReportDetails(String rid) throws SQLException {
		PreparedStatement statement = null;
		String query = "select * from "+DatabaseScheme.SurveyReport.table_name+" where rid='"+rid+"'";
		ArrayList<String> res = new ArrayList<String>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			int i=1;
			rs.next();
			while(i<10)
				res.add(rs.getString(i++));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}
/**
 * Update board request for additional data
 * @param list the additional data required and the request id
 * @throws IOException
 * @throws SQLException
 */
	public void addAdditionalData(ArrayList<String> list) throws IOException, SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "UPDATE "+DatabaseScheme.SurveyReport.table_name+" SET additional_data='"+list.get(1)+"' WHERE rid='"+list.get(0)+"'";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update estimate_time , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Updates survey report after editing the survey report
 * @param list all survey report fields
 * @throws SQLException
 */
	public void updateSurveyReport(ArrayList<String> list)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "Update "+DatabaseScheme.SurveyReport.table_name+" " +
				"Set "+DatabaseScheme.SurveyReport.fields[3]+"='"+list.get(0)+"'," +
				" "+DatabaseScheme.SurveyReport.fields[4]+"='"+list.get(1)+"'," +
				" "+DatabaseScheme.SurveyReport.fields[5]+"='"+list.get(2)+"'," +
				" "+DatabaseScheme.SurveyReport.fields[6]+"='"+list.get(3)+"'," +
				" "+DatabaseScheme.SurveyReport.fields[7]+"='"+list.get(4)+"'" +
				" Where rid='"+list.get(6)+"';";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update "+DatabaseScheme.SurveyReport.table_name+" , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Creates new board decision
 * @param list data to update contains request id and the decision
 * @throws SQLException
 */
	public void createBoardDecision(ArrayList<String> list) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "Insert Into "+DatabaseScheme.BoardDecision.table_name+" Values ('"+list.get(2)+"','"+list.get(0)+"','NOT')";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update BoardDecision , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Returns board decision.
 * @param rid request id
 * @return board deciasion
 * @throws SQLException
 */
	public String getBoardDecision(String rid)throws SQLException {
		PreparedStatement statement = null;
		String query = "select decision from "+DatabaseScheme.BoardDecision.table_name+" where rid='"+rid+"'";
		String res = new String();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			rs.next();
			res=rs.getNString("decision");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}
/**
 * Updates approved board decision
 * @param list - the decision and the request id relevant
 * @throws SQLException
 */
	public void approveBoardDecision(ArrayList<String> list) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "Update "+DatabaseScheme.BoardDecision.table_name+" " +
				"Set "+DatabaseScheme.BoardDecision.fields[1]+"='"+list.get(1)+"'," +
				" "+DatabaseScheme.BoardDecision.fields[2]+"='YES'"+
				" Where rid='"+list.get(2)+"';";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update "+DatabaseScheme.BoardDecision.table_name+" , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Returns the extension requests list.
 * @return extension requests list.
 */
	public ArrayList<ExtensionRequest> loadExtensionRequests() {
		PreparedStatement statement = null;
		String query = "select * from "+DatabaseScheme.ExtensionRequest.table_name+" where decision = 0";
		ArrayList<ExtensionRequest> res = new ArrayList<ExtensionRequest>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			while(rs.next())
				res.add(new ExtensionRequest(rs));
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;

	}
/**
 * Returns tester's report
 * @param rid request id
 * @return tester's report
 * @throws SQLException
 */
	public  ArrayList<String>  getTesterReport(String rid) throws SQLException {
		PreparedStatement statement = null;
		String query = "select test_date,uid,failures_description from "+DatabaseScheme.ExecutionFailures.table_name+" " +
				"where rid='"+rid+"' and efid=(select MAX(efid) from execution_failures where rid='"+rid+"')";
		ArrayList<String>  res = new  ArrayList<String> ();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()){
				res.add(new String(rid));
				res.add(rs.getString("uid"));
				res.add(rs.getString("test_date"));
				res.add(rs.getString("failures_description"));
			}
			else return null;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return res;
	}
/**
 * Create new tester failures report
 * @param list request id, test date, user id, description 
 * @return long generatedKeys failures report.
 * @throws SQLException
 */
	public long createFailureReport(ArrayList<String> list) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO "+DatabaseScheme.ExecutionFailures.table_name+" (rid,test_date,uid,failures_description) " +
				"VALUES ('"+list.get(1)+"',CURDATE(),'"+list.get(2)+"','"+list.get(0)+"');";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			if (affectedRows == 0)
				throw new SQLException(
						"Creating FailureReport failed, no rows affected.");
			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			} else {
				throw new SQLException(
						"Creating FailureReport failed, no generated key obtained.");
			}
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}
/**
 * Sends notification to request owner by mail
 * @param reqID request id
 * @param mssg message to send.
 */
	public void notifyOwnerByMail(String reqID,String mssg)  {
		PreparedStatement statement = null;
		String query = "select us.Email from "+DatabaseScheme.ChangeRequest.table_name+" ch,"+DatabaseScheme.Users.table_name+" us where ch.rid='"+reqID+"' and ch.ownerid=us.uid";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()){
				String toMail=rs.getString("Email");
				new Thread(EmailSenderAPI.send(toMail, "Your Change Request "+reqID, mssg)).run();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Updates supervision action.
 * @param rid request id
 * @param upaction action id
 * @param uid user id
 * @return long  generatedKeys fro SQL
 * @throws SQLException
 */
	public long updateSupervisorAction(String rid,String upaction, String uid) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String query = "INSERT INTO "+DatabaseScheme.SupervisorRecords.table_name+" (rid,upaction,uid,date) " +
				"VALUES ('"+rid+"','"+upaction+"','"+uid+"',CURDATE());";
		try {
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,
					Statement.RETURN_GENERATED_KEYS);
			if (affectedRows == 0)
				throw new SQLException(
						"Creating SupervisorRecords failed, no rows affected.");
			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				return generatedKeys.getLong(1);
			} else {
				throw new SQLException(
						"Creating SupervisorRecords failed, no generated key obtained.");
			}
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}
/**
 * Moves request to frozen state
 * @param rid request id
 * @throws SQLException
 */
	public void freezeRequest(String rid) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String eidQuery = "(Select eid from "+DatabaseScheme.StatusExecution.table_name+" " +
				"WHERE rid='"+rid+"' and date_finished is NULL)";
		String query = "Insert Into "+DatabaseScheme.StatusExecutionFreezed.table_name+" " +
				"Values ('"+rid+"'," +
				"(Select sid from "+DatabaseScheme.ChangeRequest.table_name+" where rid='"+rid+"')," +
				""+eidQuery+",CURDATE(),null)";

		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update StatusExecutionFreezed , no rows affected.");
			else new Thread (UpdateManagerForSuspended(rid)).start();

		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
	/**
	 * unfreeze request and update status and current stage time
	 * @param rid request id
	 * @return oldSidandtime old status and time of the request
	 * @throws SQLException
	 */
	public String UnfreezeRequest(String rid) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String oldSidandtime="Select sid,DATEDIFF(CURDATE(),date_freezed_started) from "+DatabaseScheme.StatusExecutionFreezed.table_name+" where rid='"+rid+"' and date_freezed_finished is NULL";
		String oldtime="Select estimate_time from "+DatabaseScheme.StatusExecution.table_name+" where rid='"+rid+"' and date_finished is NULL";
		String updateFinishFreezed="Update "+DatabaseScheme.StatusExecutionFreezed.table_name+" Set date_freezed_finished=CURDATE() where rid='"+rid+"' and date_freezed_finished is NULL";	
		String addtime="";

		try {
			statement = conn.prepareStatement(oldSidandtime);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(oldSidandtime);
			if(rs.next()){
				oldSidandtime=rs.getString("sid");
				addtime=rs.getString(2);
			}
			statement = conn.prepareStatement(oldtime);
			statement.execute(useQuery);
			rs = statement.executeQuery(oldtime);
			if(rs.next()){
				oldtime=rs.getString(1);
				addtime=String.valueOf(Integer.parseInt(oldtime)+Integer.parseInt(addtime));
				String updateTime="UPDATE "+DatabaseScheme.StatusExecution.table_name+" SET estimate_time="+addtime+" WHERE rid='"+rid+"' and date_finished is NULL";
				statement = conn.prepareStatement(updateTime);
				statement.execute(useQuery);
				int affectedRows = statement.executeUpdate(updateTime);
				if (affectedRows == 0)
					throw new SQLException(
							"Cant update estimate_time , no rows affected.");
			}
			statement = conn.prepareStatement(updateFinishFreezed);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(updateFinishFreezed);
			if (affectedRows == 0)
				throw new SQLException("Cant updateFinishFreezed , no rows affected.");
			else return oldSidandtime;

		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Returns connection to db
 * @return connection to db
 * @throws SQLException
 */
	public static Connection getConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		conn.prepareStatement(useQuery).execute();
		return conn;
	}
/**
 * Returns the closing status of request
 * @param rid request id
 * @return closing status of request.
 */
	public String getCorrectStatus(String rid)  {
		PreparedStatement statement = null;
		String query = "select rid from "+DatabaseScheme.StatusExecution.table_name+" where rid='"+rid+"' and sid='Testing Started'";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if(rs.next())
				return Statuses.Closed_Executed;
			else return Statuses.Closed_Rejected;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;
	}
/**
 * Returns request's details for monitoing
 * @param rid request id
 * @return request details
 */
	public HashMap<String,String> getRequestDetails_new(long rid) {
		HashMap<String,String> result = new HashMap<String,String>();
		Calendar cal = Calendar.getInstance();
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
		Date date;
		PreparedStatement statement = null;
		String query = "select ch.title,ch.rid,ch.sid "+
				" from "+DatabaseScheme.ChangeRequest.table_name+" ch," +
				DatabaseScheme.StatusExecution.table_name+" su "+ 
				"where ch.rid='"+rid+"'";
		String querydate = "select su.date_started,su.estimate_time,su.extension_time from "
				+DatabaseScheme.ChangeRequest.table_name+" ch, "+DatabaseScheme.StatusExecution.table_name+" su " +
				" where su.sid = ch.sid and su.rid="+rid+" and ch.rid="+rid;
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(connectURL);
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if(rs.next()){
				result.put("Request ID ", rs.getString("rid"));
				result.put("Request Title ", rs.getString("title"));
				result.put("Request Status ", rs.getString("sid"));

				if (rs.getString("sid").equals(Statuses.New_Request))
					result.put("Estimated finish", "New Request, not estimated");
				else if (rs.getString("sid").equals(Statuses.Closed_Executed))
					result.put("Estimated finish", "Finished, modification complete");
				else if (rs.getString("sid").equals(Statuses.Appoint_Tester))
					result.put("Estimated finish", "Awaiting Tester appointment");
				else if (rs.getString("sid").equals(Statuses.Closed_Rejected))
					result.put("Estimated finish", "Request rejected");
				else if (rs.getString("sid").equals(Statuses.SUSPENDED))
					result.put("Estimated finish", "Request SUSPENDED");
				else if ((rs.getString("sid").equals(Statuses.Estimate_Timeline_Execution))||(rs.getString("sid").equals(Statuses.Estimate_Timeline_Surveying)))
					result.put("Estimated finish", "Waiting timeline approval");
				else if ((rs.getString("sid").equals(Statuses.Approve_Execution_Time))||(rs.getString("sid").equals(Statuses.Approve_Surveying_Time)))
					result.put("Estimated finish", "Waiting timeline approval");

				else
				{
					statement.execute(useQuery);
					ResultSet rs2 = statement.executeQuery(querydate);
					if (rs2.next())
					{
						date = rs2.getDate(("date_started"));
						cal.setTime(date);
						if (rs2.getString("extension_time")!= null)
							cal.add(Calendar.DATE,rs2.getInt("estimate_time")+rs2.getInt("extension_time"));
						else
							cal.add(Calendar.DATE,rs2.getInt("estimate_time"));
						result.put("Estimated stage finish:" ,dateFormat.format(cal.getTime()));
					}
				}
				return result;
			}
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;

	}
	/**
	 * sends message to manager about suspended request
	 * @param rid - the suspended request
	 * @return Runnable
	 */
	public synchronized Runnable UpdateManagerForSuspended(final String rid) {
		return (new Runnable ()
			      {
			     	@Override
					public void run(){
			     		PreparedStatement statement = null;
			     		String queryForInfo = "select uid from users where tid='manager'";
			     		Connection conn = null;
			     		try {
			     			conn = DriverManager.getConnection(connectURL);
			     			statement = conn.prepareStatement(queryForInfo);
			     			statement.execute(useQuery);
			     			ResultSet usersName = statement.executeQuery(queryForInfo);
			     			if (usersName.next())
			     				MessegesAPI.sendSuspendedMessege(usersName.getString("uid"), rid);
			     		} catch (SQLException e) {
			     			e.printStackTrace();
			     		} finally {
			     			if (statement != null)
			     				try {
			     					statement.close();
			     				} catch (SQLException logOrIgnore) {
			     				}
			     			if (conn != null)
			     				try {
			     					conn.close();
			     				} catch (SQLException logOrIgnore) {
			     				}
			     		}
			     	}
			      });
		}

/**
 * Changes extension request decision
 * @param values new data
 * @return String failed or succided
 * @throws SQLException
 */
	public String updateExtensionRequestStatus(HashMap<String, String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		int decision = -1,check_1 = 0,check_2 = 0;
		String days =(values.get("extension_days"));

		if (values.get("decision").equals("Approve"))
			decision = 1;
         
		String querynew ="UPDATE "+DatabaseScheme.ExtensionRequest.table_name+" SET decision='"+decision+ 
				"' WHERE rid='"+values.get("rid")+"' and sid ='"+values.get("sid")+"';";
		String query = "UPDATE "+DatabaseScheme.StatusExecution.table_name+" SET extension_time= "+
				days+" WHERE rid='"+values.get("rid")+"' and sid='"+values.get("sid")+"';"; 
		try {
			if (decision == 1)
			{
				String manager = "select uid from users where tid = 'manager';";
				statement = conn.prepareStatement(query);
				statement.execute(useQuery);
				int affectedRows = statement.executeUpdate(query);
				check_1 = affectedRows;
				statement = conn.prepareStatement(manager);
				statement.execute(useQuery);
				ResultSet man = statement.executeQuery(manager);
				if (man.next())
					 MessegesAPI.sendExtensionApprovedMessege(man.getString("uid"), values.get("rid"));
				if (affectedRows == 0)
					throw new SQLException(
							"Cant update req status, no rows affected.");
			}
			statement = conn.prepareStatement(querynew);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(querynew);
			check_2 = affectedRows;
			if (affectedRows == 0)
				throw new SQLException(
						"Cant update req status, no rows affected.");
			
			if ((check_2 == 0)&&(check_1 == 0))
				return P.error;
			else
				return P.UpdateComplete;

		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}
/**
 * Deletes message for worker
 * @param msg message
 * @throws SQLException
 */
	public  void deleteMssg(Messege msg)throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "Delete from messeges " +
						"where uid='"+msg.getUid()+"' and eid='"+msg.getEid()+"' and rid='"+msg.getRid()+"' " +
								"and mtype=(select mtid from mssgs_types where mssg_title='"+msg.getMssg()+"');";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query);
			if (affectedRows == 0)
				throw new SQLException(
						"Cant Delete messeges , no rows affected.");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

	/**
	 * insert into periodic reports new report and update its values 
	 * @param values - the date and time of the new periodic report
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public void updatePeriodicReport(ArrayList<String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
	
		Date date = new Date();
		DateFormat dateFormat = new SimpleDateFormat("HH:mm:ss");
		String query = "INSERT INTO "+DatabaseScheme.PeriodicReports.table_name+" (from_date,to_date,time_created) values " +
				"('"+values.get(1)+"','"+values.get(2)+"','"+dateFormat.format(date)+"') " ;

		try {
			statement = conn.prepareStatement(query,statement.RETURN_GENERATED_KEYS);
			statement.execute(useQuery);
			int affectedRows = statement.executeUpdate(query,statement.RETURN_GENERATED_KEYS);
	
	
			if (affectedRows == 0)
				throw new SQLException(
					" Cannot update report table");
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		
		
	}
	/**
	 * returns the periodic report that asked for
	 * @param values - the periodic report that needed
	 * @return ResultSet the periodic report data information
	 * @throws SQLException
	 */
	public ResultSet findPeriodicReport(ArrayList<String> values) throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		String query = "SELECT * FROM (tableName) where from ='"+values.get(1)+"' and to ='"+values.get(2)+"'";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			if (rs.next())
				return rs;
			else
				return null;
				
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		
	}

	/**
	 * Get all periodic reports saved in system for re-view
	 * @return ArrayList of PeriodicReport
	 * @throws SQLException
	 */
	public ArrayList<PeriodicReport> getPeriodicReports() throws SQLException {
		Connection conn = DriverManager.getConnection(connectURL);
		PreparedStatement statement = null;
		ArrayList<PeriodicReport> values = new ArrayList<PeriodicReport>();
		String query = "SELECT * FROM periodic_reports";
		try {
			statement = conn.prepareStatement(query);
			statement.execute(useQuery);
			ResultSet rs = statement.executeQuery(query);
			while (rs.next())
			values.add(new PeriodicReport(rs));	
			
			return values;
				
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return null;
	}
}
